{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.append(\"..\")   #也可以这样\n",
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Engine(mysql://sucai_bi:***@bi.fjtayun.com:12322/sucai_bi?charset=utf8mb4)"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from db_tools import MysqlTool\n",
    "mysql_db = MysqlTool()\n",
    "mysql_db.engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "824"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pandas import read_sql\n",
    "\n",
    "# 查询出,in_type这个字段在这个范围内[1028,1039,1034,1032,1033,1008,1023,1040]\n",
    "# 且表的名字是 pay , 且 is_matched = 0 的数据\n",
    "# 或是title = \"搜索\"的数据\n",
    "# 1039,活动弹窗的数据不需要分析\n",
    "sql = \"SELECT * FROM pay WHERE in_type IN (1008,1028,1034,1032,1033,1008,1023,1040) AND is_matched = 0\"\n",
    "\n",
    "df = read_sql(sql, mysql_db.engine)\n",
    "len(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['course', 'app_tool', '', 'hetong'], dtype=object)"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看 key 有几种\n",
    "df.key.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': 349696,\n",
       " 'order_no': 'scsq0420240524001424',\n",
       " 'user_id': 2214580,\n",
       " 'vip_id': 100,\n",
       " 'course_id': 0,\n",
       " 'article_id': 0,\n",
       " 'status': '成功',\n",
       " 'channel': 'huawei',\n",
       " 'real_amount': 188.0,\n",
       " 'from': '安卓',\n",
       " 'payway': '支付宝',\n",
       " 'create_time': 1716480864,\n",
       " 'version_code': 31,\n",
       " 'in_type': 1033,\n",
       " 'buy_type': '购买VIP',\n",
       " 'key': 'course',\n",
       " 'title': '免费试看',\n",
       " 'create_time_format': Timestamp('2024-05-24 00:14:24'),\n",
       " 'is_matched': 0}"
      ]
     },
     "execution_count": 170,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 取出第一条数据\n",
    "order_info = df.iloc[0].to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://scapi.tayunapi.com/funcLog/logs?start=2024-05-23&end=2024-05-24&page=1&rows=10&user_id=2214580&sort=desc&orderby=id&create_time=1716480864'"
      ]
     },
     "execution_count": 171,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 要有办法判断到底是要解析到哪里?\n",
    "from tools.base_tools import get_current_and_previous_day_strings\n",
    "\n",
    "days_filter = get_current_and_previous_day_strings(order_info[\"create_time\"])\n",
    "\n",
    "api_url = f\"https://scapi.tayunapi.com/funcLog/logs?start={days_filter[0]}&end={days_filter[1]}&page=1&rows=10&user_id={order_info['user_id']}&sort=desc&orderby=id&create_time={order_info['create_time']}\"\n",
    "\n",
    "api_url"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "\n",
    "url = \"https://scapi.tayunapi.com/funcLog/logs\"\n",
    "\n",
    "params = {\n",
    "    \"start\": days_filter[0],\n",
    "    \"end\": days_filter[1],\n",
    "    \"page\": 1,\n",
    "    \"rows\": 3,\n",
    "    \"user_id\": order_info[\"user_id\"],\n",
    "    \"sort\": \"desc\",\n",
    "    \"orderby\": \"id\",\n",
    "    \"create_time\": order_info[\"create_time\"],\n",
    "    \"action\": order_info[\"key\"]\n",
    "}\n",
    "\n",
    "response = requests.get(url, params=params)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandas import DataFrame\n",
    "\n",
    "action_info = {}\n",
    "\n",
    "for action in response.json()[\"list\"]:\n",
    "    if action[\"action\"] == \"buy_vip\":\n",
    "        continue\n",
    "    else:\n",
    "        action_info = action\n",
    "        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': 390145,\n",
       " 'func_code': 'course|course|38|2',\n",
       " 'module': 'course',\n",
       " 'action': 'course',\n",
       " 'item_id': 38,\n",
       " 'item_custom': 2,\n",
       " 'from': 3,\n",
       " 'user_id': 2214580,\n",
       " 'click_num': 1,\n",
       " 'uuid': 'ffffffff-f850-f3f5-ffff-ffffef05ac4a',\n",
       " 'channel': 'huawei',\n",
       " 'verid': 31,\n",
       " 'is_vip': 0,\n",
       " 'create_time': 1716480586,\n",
       " 'order_no': 'scsq0420240524001424'}"
      ]
     },
     "execution_count": 175,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "action_info[\"order_no\"] = order_info[\"order_no\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [],
   "source": [
    "api_routes = {\n",
    "    1008: {\"api_url\": \"https://scapi.tayunapi.com/funcLog/searchList\"},  # 搜索\n",
    "    1028: {\"api_url\": \"https://scapi.tayunapi.com/funcLog/chatgpt\"},  # 文案创作\n",
    "    1034: {\"api_url\": \"https://scapi.tayunapi.com/funcLog/article\"},  # 文章\n",
    "    1032: {\"api_url\": \"https://scapi.tayunapi.com/funcLog/course\"},  # 素材包\n",
    "    1033: {\"api_url\": \"https://scapi.tayunapi.com/funcLog/course\"},  # 素材包\n",
    "    1023: {\"api_url\": \"https://scapi.tayunapi.com/funcLog/contract\"},  # 合同\n",
    "    1040: {\"api_url\": \"https://scapi.tayunapi.com/funcLog/video\"},  # 视频\n",
    "}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [],
   "source": [
    "api_url_desc = api_routes[order_info[\"in_type\"]][\"api_url\"]\n",
    "\n",
    "res_desc = requests.get(f\"{api_url_desc}?data_id={action_info['item_id']}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'detail_id': 38,\n",
       " 'info_title': '美食素材',\n",
       " 'cover': 'http://scfile.chaotuapp.com/course/20230602/f8d46a3bef84b0dffff043b49277faef.jpg'}"
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_info_desc = res_desc.json()[\"list\"][0]\n",
    "item = {\n",
    "    \"detail_id\": data_info_desc[\"id\"],\n",
    "    \"info_title\": data_info_desc[\"title\"],\n",
    "    \"cover\": data_info_desc[\"cover\"],\n",
    "}\n",
    "item"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [],
   "source": [
    "info_data = {**item, **action_info}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [],
   "source": [
    "info_data\n",
    "\n",
    "keys = [\n",
    "    \"order_no\",\n",
    "    \"detail_id\",\n",
    "    \"info_title\",\n",
    "    \"cover\",\n",
    "    \"id\",\n",
    "    \"module\",\n",
    "    \"action\",\n",
    "    \"item_custom\",\n",
    "    \"create_time\",\n",
    "]\n",
    "selected_data = {k: info_data[k] for k in keys if k in info_data}\n",
    "\n",
    "\n",
    "# 把key进行重命名一下\n",
    "if \"create_time\" in selected_data:\n",
    "    selected_data[\"action_create_time\"] = selected_data.pop(\"create_time\")\n",
    "\n",
    "if \"id\" in selected_data:\n",
    "    selected_data[\"action_id\"] = selected_data.pop(\"id\")\n",
    "\n",
    "selected_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'order_no': 'scsq0420240524001424',\n",
       " 'detail_id': 38,\n",
       " 'info_title': '美食素材',\n",
       " 'cover': 'http://scfile.chaotuapp.com/course/20230602/f8d46a3bef84b0dffff043b49277faef.jpg',\n",
       " 'module': 'course',\n",
       " 'action': 'course',\n",
       " 'item_custom': 2,\n",
       " 'action_create_time': 1716480586,\n",
       " 'action_id': 390145}"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_res = DataFrame([selected_data])\n",
    "mysql_db.upsert_df(df_res, table_name=\"course_pay_info\", primary_key=\"order_no\")\n",
    "\n",
    "\n",
    "# 把 is_matched = 1 更新,用sql语句\n",
    "mysql_db.execute_sql(\n",
    "    f\"UPDATE pay SET is_matched = 1 WHERE order_no = '{order_info['order_no']}'\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"UPDATE pay SET is_matched = 1 WHERE order_no = 'scsq0320240524001131'\""
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f\"UPDATE pay SET is_matched = 1 WHERE order_no = '{order_info['order_no']}'\""
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "sucai_bi",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
